const { pool, query: dbQuery } = require('../config/database');
const { logger } = require('../utils/logger');
const { v4: uuidv4 } = require('uuid');

/**
 * 导入任务模型类
 * 处理Excel导入任务的状态管理、Dry-Run和事务提交
 */
class ImportJob {
  constructor(data = {}) {
    this.id = data.id;
    this.job_id = data.job_id;
    this.import_type = data.import_type;
    this.admin_id = data.admin_id;
    this.file_name = data.file_name;
    this.file_path = data.file_path;
    this.file_size = data.file_size;
    this.status = data.status || 'uploaded';
    this.total_rows = data.total_rows || 0;
    this.valid_rows = data.valid_rows || 0;
    this.error_rows = data.error_rows || 0;
    this.warning_rows = data.warning_rows || 0;
    this.new_records = data.new_records || 0;
    this.updated_records = data.updated_records || 0;
    this.validation_errors = data.validation_errors;
    this.error_file_path = data.error_file_path;
    this.snapshot_data = data.snapshot_data;
    this.dry_run_result = data.dry_run_result;
    this.started_at = data.started_at;
    this.completed_at = data.completed_at;
    this.created_at = data.created_at;
    this.updated_at = data.updated_at;
  }

  /**
   * 创建新的导入任务
   */
  static async create(jobData) {
    try {
      const {
        import_type,
        admin_id,
        file_name,
        file_path,
        file_size = 0
      } = jobData;

      const job_id = uuidv4();

      const result = await dbQuery(
        `INSERT INTO import_jobs (
          job_id, import_type, admin_id, file_name, file_path, file_size, status
        ) VALUES (?, ?, ?, ?, ?, ?, 'uploaded')`,
        [job_id, import_type, admin_id, file_name, file_path, file_size]
      );

      return await ImportJob.findByJobId(job_id);
    } catch (error) {
      logger.error('创建导入任务失败:', error);
      throw error;
    }
  }

  /**
   * 根据ID查找导入任务
   */
  static async findById(id) {
    try {
      const rows = await dbQuery(
        'SELECT * FROM import_jobs WHERE id = ?',
        [id]
      );

      if (rows.length === 0) {
        return null;
      }

      const job = new ImportJob(rows[0]);
      // 安全解析JSON字段
      if (job.validation_errors) {
        try {
          job.validation_errors = JSON.parse(job.validation_errors);
        } catch (parseError) {
          logger.warn(`解析validation_errors失败，保持原始值: ${parseError.message}`);
          // 如果解析失败，保持原始字符串值，不进行转换
        }
      }
      if (job.snapshot_data) {
        try {
          job.snapshot_data = JSON.parse(job.snapshot_data);
        } catch (parseError) {
          logger.warn(`解析snapshot_data失败: ${parseError.message}`);
        }
      }
      if (job.dry_run_result) {
        try {
          job.dry_run_result = JSON.parse(job.dry_run_result);
        } catch (parseError) {
          logger.warn(`解析dry_run_result失败: ${parseError.message}`);
        }
      }

      return job;
    } catch (error) {
      logger.error('查找导入任务失败:', error);
      throw error;
    }
  }

  /**
   * 根据job_id查找导入任务
   */
  static async findByJobId(job_id) {
    try {
      const rows = await dbQuery(
        'SELECT * FROM import_jobs WHERE job_id = ?',
        [job_id]
      );

      if (rows.length === 0) {
        return null;
      }

      const job = new ImportJob(rows[0]);
      // 安全解析JSON字段
      if (job.validation_errors) {
        try {
          job.validation_errors = JSON.parse(job.validation_errors);
        } catch (parseError) {
          logger.warn(`解析validation_errors失败，保持原始值: ${parseError.message}`);
          // 如果解析失败，保持原始字符串值，不进行转换
        }
      }
      if (job.snapshot_data) {
        try {
          job.snapshot_data = JSON.parse(job.snapshot_data);
        } catch (parseError) {
          logger.warn(`解析snapshot_data失败: ${parseError.message}`);
        }
      }
      if (job.dry_run_result) {
        try {
          job.dry_run_result = JSON.parse(job.dry_run_result);
        } catch (parseError) {
          logger.warn(`解析dry_run_result失败: ${parseError.message}`);
        }
      }

      return job;
    } catch (error) {
      logger.error('查找导入任务失败:', error);
      throw error;
    }
  }

  /**
   * 获取管理员的导入任务列表
   */
  static async findByAdminId(admin_id, options = {}) {
    try {
      const {
        page = 1,
        limit = 20,
        import_type,
        status,
        sort_by = 'created_at',
        sort_order = 'DESC'
      } = options;

      let query = 'SELECT * FROM import_jobs WHERE admin_id = ?';
      const params = [admin_id];

      if (import_type) {
        query += ' AND import_type = ?';
        params.push(import_type);
      }

      if (status) {
        query += ' AND status = ?';
        params.push(status);
      }

      // 添加排序
      const validSortFields = ['id', 'created_at', 'updated_at', 'status'];
      const sortField = validSortFields.includes(sort_by) ? sort_by : 'created_at';
      const sortDirection = sort_order.toUpperCase() === 'ASC' ? 'ASC' : 'DESC';
      query += ` ORDER BY ${sortField} ${sortDirection}`;

      // 添加分页
      const offset = (page - 1) * limit;
      query += ` LIMIT ${limit} OFFSET ${offset}`;

      const rows = await dbQuery(query, params);
      return rows.map(row => new ImportJob(row));
    } catch (error) {
      logger.error('获取导入任务列表失败:', error);
      throw error;
    }
  }

  /**
   * 更新任务状态
   */
  async updateStatus(status, additionalData = {}) {
    try {
      const updateFields = ['status = ?'];
      const params = [status];

      // 添加额外的更新字段
      Object.keys(additionalData).forEach(key => {
        if (key === 'validation_errors' || key === 'snapshot_data' || key === 'dry_run_result') {
          updateFields.push(`${key} = ?`);
          params.push(JSON.stringify(additionalData[key]));
        } else {
          updateFields.push(`${key} = ?`);
          params.push(additionalData[key]);
        }
      });

      // 根据状态设置时间戳
      if (status === 'parsing' || status === 'validating') {
        updateFields.push('started_at = CURRENT_TIMESTAMP');
      } else if (status === 'completed' || status === 'failed') {
        updateFields.push('completed_at = CURRENT_TIMESTAMP');
      }

      params.push(this.id);

      await dbQuery(
        `UPDATE import_jobs SET ${updateFields.join(', ')}, updated_at = CURRENT_TIMESTAMP WHERE id = ?`,
        params
      );

      // 更新本地属性
      this.status = status;
      Object.assign(this, additionalData);

      return true;
    } catch (error) {
      logger.error('更新任务状态失败:', error);
      throw error;
    }
  }

  /**
   * 保存Dry-Run结果
   */
  async saveDryRunResult(result) {
    try {
      const {
        total_rows,
        valid_rows,
        error_rows,
        warning_rows,
        new_records,
        updated_records,
        validation_errors,
        error_file_path
      } = result;

      await this.updateStatus('dry_run_complete', {
        total_rows,
        valid_rows,
        error_rows,
        warning_rows,
        new_records,
        updated_records,
        validation_errors,
        error_file_path,
        dry_run_result: result
      });

      return true;
    } catch (error) {
      logger.error('保存Dry-Run结果失败:', error);
      throw error;
    }
  }

  /**
   * 保存导入前快照
   */
  async saveSnapshot(snapshotData) {
    try {
      await this.updateStatus(this.status, {
        snapshot_data: snapshotData
      });

      return true;
    } catch (error) {
      logger.error('保存快照失败:', error);
      throw error;
    }
  }

  /**
   * 检查任务是否可以提交
   */
  canCommit() {
    return this.status === 'dry_run_complete' && this.error_rows === 0;
  }

  /**
   * 检查任务是否可以回滚
   */
  canRollback() {
    return this.status === 'completed' && this.snapshot_data;
  }

  /**
   * 获取任务统计信息
   */
  getStats() {
    return {
      total_rows: this.total_rows,
      valid_rows: this.valid_rows,
      error_rows: this.error_rows,
      warning_rows: this.warning_rows,
      new_records: this.new_records,
      updated_records: this.updated_records,
      success_rate: this.total_rows > 0 ? (this.valid_rows / this.total_rows * 100).toFixed(2) : 0
    };
  }

  /**
   * 删除导入任务（软删除）
   */
  async delete() {
    try {
      await dbQuery(
        'UPDATE import_jobs SET status = "failed", updated_at = CURRENT_TIMESTAMP WHERE id = ?',
        [this.id]
      );

      return true;
    } catch (error) {
      logger.error('删除导入任务失败:', error);
      throw error;
    }
  }

  /**
   * 获取导入任务统计
   */
  static async getStats(admin_id = null, days = 30) {
    try {
      let query = `
        SELECT 
          COUNT(*) as total_jobs,
          COUNT(CASE WHEN status = 'completed' THEN 1 END) as completed_jobs,
          COUNT(CASE WHEN status = 'failed' THEN 1 END) as failed_jobs,
          COUNT(CASE WHEN status IN ('uploaded', 'parsing', 'validating', 'committing') THEN 1 END) as pending_jobs,
          AVG(CASE WHEN status = 'completed' THEN TIMESTAMPDIFF(SECOND, started_at, completed_at) END) as avg_processing_time,
          SUM(total_rows) as total_processed_rows,
          SUM(new_records) as total_new_records,
          SUM(updated_records) as total_updated_records
        FROM import_jobs 
        WHERE created_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
      `;
      const params = [days];

      if (admin_id) {
        query += ' AND admin_id = ?';
        params.push(admin_id);
      }

      const rows = await dbQuery(query, params);
      return rows[0];
    } catch (error) {
      logger.error('获取导入统计失败:', error);
      throw error;
    }
  }
}

module.exports = ImportJob;